8 Working with Time – Workbook for Introductory SQL

8.1 Reference

Default database in this chapter is “ap”.

Other databases included in the simulation MySQL server:

om
ex

8.2 Data Type

5 Different temporal data types:

Time (2:22:37)
DateTime (2028-10-15 2:22:37)
TimeStamp (2028-10-15 2:22:37 UTC)
Date (2028-10-15)
Year (2028)

Note: Value stored in the database and displayed value are 2 different things.

For example, all of the following displayed dates have the same stored values in the database:

‘2028-08-15’
‘2028-8-15’
‘28-8-15’
‘20280815’

All of these stored as 2028-8-15 in the database

8.3 Working with Time

Get Current Date and Time

For more information about UTC (Coordinated Universal Time).

UTC

SELECT 
  NOW(),
  SYSDATE(),
  CURRENT_TIMESTAMP(),
  CURDATE(),
  CURRENT_DATE(),
  CURTIME(),
  CURRENT_TIME(),
  UTC_DATE(),
  UTC_TIME()

1 records
NOW()	SYSDATE()	CURRENT_TIMESTAMP()	CURDATE()	CURRENT_DATE()	CURTIME()	CURRENT_TIME()	UTC_DATE()	UTC_TIME()
2024-11-09 13:53:24	2024-11-09 13:53:24	2024-11-09 13:53:24	2024-11-09	2024-11-09	13:53:24	13:53:24	2024-11-09	18:53:24

8.4 Extract Time Component

SELECT
  invoice_due_date,
  DAYOFMONTH(invoice_due_date),
  MONTH(invoice_due_date),
  YEAR(invoice_due_date),
  HOUR(invoice_due_date),
  MINUTE(invoice_due_date),
  SECOND(invoice_due_date),
  DAYOFWEEK(invoice_due_date),
  QUARTER(invoice_due_date),
  DAYOFYEAR(invoice_due_date),
  WEEK(invoice_due_date),
  LAST_DAY(invoice_due_date),
  DAYNAME(invoice_due_date),
  MONTHNAME(invoice_due_date)
FROM invoices

Displaying records 1 - 15
invoice_due_date	DAYOFMONTH(invoice_due_date)	MONTH(invoice_due_date)	YEAR(invoice_due_date)	DAYOFWEEK(invoice_due_date)	QUARTER(invoice_due_date)	DAYOFYEAR(invoice_due_date)	WEEK(invoice_due_date)	LAST_DAY(invoice_due_date)	DAYNAME(invoice_due_date)	MONTHNAME(invoice_due_date)
2022-05-08	8	5	2022	1	2	128	19	2022-05-31	Sunday	May
2022-05-10	10	5	2022	3	2	130	19	2022-05-31	Tuesday	May
2022-05-13	13	5	2022	6	2	133	19	2022-05-31	Friday	May
2022-05-16	16	5	2022	2	2	136	20	2022-05-31	Monday	May
2022-05-16	16	5	2022	2	2	136	20	2022-05-31	Monday	May
2022-05-16	16	5	2022	2	2	136	20	2022-05-31	Monday	May
2022-05-21	21	5	2022	7	2	141	20	2022-05-31	Saturday	May
2022-05-04	4	5	2022	4	2	124	18	2022-05-31	Wednesday	May
2022-05-24	24	5	2022	3	2	144	21	2022-05-31	Tuesday	May
2022-05-24	24	5	2022	3	2	144	21	2022-05-31	Tuesday	May
2022-05-25	25	5	2022	4	2	145	21	2022-05-31	Wednesday	May
2022-05-16	16	5	2022	2	2	136	20	2022-05-31	Monday	May
2022-05-20	20	5	2022	6	2	140	20	2022-05-31	Friday	May
2022-06-10	10	6	2022	6	2	161	23	2022-06-30	Friday	June
2022-06-02	2	6	2022	5	2	153	22	2022-06-30	Thursday	June

Format Date and Time

For all different time formatting details in MySQL, see Oracle MySQL documentation .

SELECT
  invoice_due_date,
  DATE_FORMAT(invoice_due_date, '%m') AS MONTH_ONLY,
  DATE_FORMAT(invoice_due_date, '%M') AS MONTH_NAME,
  DATE_FORMAT(invoice_due_date, '%b-%d-%Y') AS MMM_DD_YYYY
FROM invoices

Displaying records 1 - 15
invoice_due_date	MONTH_ONLY	MONTH_NAME	MMM_DD_YYYY
2022-05-08	05	May	May-08-2022
2022-05-10	05	May	May-10-2022
2022-05-13	05	May	May-13-2022
2022-05-16	05	May	May-16-2022
2022-05-16	05	May	May-16-2022
2022-05-16	05	May	May-16-2022
2022-05-21	05	May	May-21-2022
2022-05-04	05	May	May-04-2022
2022-05-24	05	May	May-24-2022
2022-05-24	05	May	May-24-2022
2022-05-25	05	May	May-25-2022
2022-05-16	05	May	May-16-2022
2022-05-20	05	May	May-20-2022
2022-06-10	06	June	Jun-10-2022
2022-06-02	06	June	Jun-02-2022

Date Time Calculation

SELECT
  invoice_due_date,
  payment_date,
  DATE_ADD(invoice_due_date,INTERVAL 9 MONTH),
  DATE_SUB(invoice_due_date,INTERVAL 5 DAY),
  DATEDIFF(invoice_due_date, payment_date),
  -- very useful to ensure the calculation results in days
  -- by default, it is calculating number of days passed by between the specified date and year 0
  TO_DAYS(invoice_due_date)
FROM invoices
ORDER BY invoice_due_date

Displaying records 1 - 15
invoice_due_date	payment_date	DATE_ADD(invoice_due_date,INTERVAL 9 MONTH)	DATE_SUB(invoice_due_date,INTERVAL 5 DAY)	DATEDIFF(invoice_due_date, payment_date)	TO_DAYS(invoice_due_date)
2022-05-04	2022-05-01	2023-02-04	2022-04-29	3	738644
2022-05-08	2022-05-07	2023-02-08	2022-05-03	1	738648
2022-05-10	2022-05-14	2023-02-10	2022-05-05	-4	738650
2022-05-13	2022-05-09	2023-02-13	2022-05-08	4	738653
2022-05-16	2022-05-12	2023-02-16	2022-05-11	4	738656
2022-05-16	2022-05-11	2023-02-16	2022-05-11	5	738656
2022-05-16	2022-05-21	2023-02-16	2022-05-11	-5	738656
2022-05-16	2022-05-13	2023-02-16	2022-05-11	3	738656
2022-05-17	2022-05-19	2023-02-17	2022-05-12	-2	738657
2022-05-20	2022-05-23	2023-02-20	2022-05-15	-3	738660
2022-05-21	2022-05-22	2023-02-21	2022-05-16	-1	738661
2022-05-23	2022-05-22	2023-02-23	2022-05-18	1	738663
2022-05-24	2022-05-21	2023-02-24	2022-05-19	3	738664
2022-05-24	2022-05-22	2023-02-24	2022-05-19	2	738664
2022-05-25	2022-05-20	2023-02-25	2022-05-20	5	738665

TIME_TO_SEC(): Returns the time argument, converted to seconds. Returns NULL if time is NULL.

SELECT 
  NOW(),
  HOUR(NOW()) * 3600 + MINUTE(NOW()) * 60 + SECOND(NOW()),
  -- very useful to ensure the calculation results in seconds
  TIME_TO_SEC(NOW())

1 records
NOW()	HOUR(NOW()) * 3600 + MINUTE(NOW()) * 60 + SECOND(NOW())	TIME_TO_SEC(NOW())
2024-11-09 13:53:25	50005	50005

Search Date and Time

General process:

Specify conditions in WHERE clause;
Convert/Extract date/time field to a particular format that suits your search need;
Use convered/extracted values to search for targeted date/time range.

For example, we would like to search for all invoices with invoice date during May 2022.

SELECT * 
FROM invoices
WHERE invoice_date BETWEEN '2022-05-01' AND '2022-05-31'
ORDER BY invoice_date DESC

Displaying records 1 - 15
invoice_id	vendor_id	invoice_number	invoice_date	invoice_total	payment_total	terms_id	invoice_due_date	payment_date
41	123	963253255	2022-05-31	53.75	53.75	3	2022-06-30	2022-06-27
42	123	94007069	2022-05-31	400.00	400.00	3	2022-06-30	2022-07-01
40	121	97/503	2022-05-30	639.77	639.77	3	2022-06-29	2022-06-25
39	110	0-2058	2022-05-28	37966.19	37966.19	3	2022-06-27	2022-06-30
38	123	963253272	2022-05-26	61.50	61.50	3	2022-06-25	2022-06-30
35	107	RTR-72-3662-X	2022-05-25	1600.00	1600.00	4	2022-07-04	2022-07-09
36	121	97/465	2022-05-25	565.15	565.15	3	2022-06-24	2022-06-24
37	123	963253260	2022-05-25	36.00	36.00	3	2022-06-24	2022-06-26
33	105	94007005	2022-05-23	220.00	220.00	3	2022-06-22	2022-06-26
34	123	963253232	2022-05-23	127.75	127.75	3	2022-06-22	2022-06-18
31	104	P02-3772	2022-05-21	7125.34	7125.34	3	2022-06-20	2022-06-24
32	121	97/486	2022-05-21	953.10	953.10	3	2022-06-20	2022-06-22
30	123	1-200-5164	2022-05-20	63.40	63.40	3	2022-06-19	2022-06-24
29	108	121897	2022-05-19	450.00	450.00	4	2022-06-28	2022-07-03
28	123	963253263	2022-05-16	109.50	109.50	3	2022-06-15	2022-06-10

Or, alternatively:

SELECT * 
FROM invoices
WHERE MONTH(invoice_date) = 5 AND YEAR(invoice_date) = 2022
ORDER BY invoice_date DESC

Displaying records 1 - 15
invoice_id	vendor_id	invoice_number	invoice_date	invoice_total	payment_total	terms_id	invoice_due_date	payment_date
41	123	963253255	2022-05-31	53.75	53.75	3	2022-06-30	2022-06-27
42	123	94007069	2022-05-31	400.00	400.00	3	2022-06-30	2022-07-01
40	121	97/503	2022-05-30	639.77	639.77	3	2022-06-29	2022-06-25
39	110	0-2058	2022-05-28	37966.19	37966.19	3	2022-06-27	2022-06-30
38	123	963253272	2022-05-26	61.50	61.50	3	2022-06-25	2022-06-30
35	107	RTR-72-3662-X	2022-05-25	1600.00	1600.00	4	2022-07-04	2022-07-09
36	121	97/465	2022-05-25	565.15	565.15	3	2022-06-24	2022-06-24
37	123	963253260	2022-05-25	36.00	36.00	3	2022-06-24	2022-06-26
33	105	94007005	2022-05-23	220.00	220.00	3	2022-06-22	2022-06-26
34	123	963253232	2022-05-23	127.75	127.75	3	2022-06-22	2022-06-18
31	104	P02-3772	2022-05-21	7125.34	7125.34	3	2022-06-20	2022-06-24
32	121	97/486	2022-05-21	953.10	953.10	3	2022-06-20	2022-06-22
30	123	1-200-5164	2022-05-20	63.40	63.40	3	2022-06-19	2022-06-24
29	108	121897	2022-05-19	450.00	450.00	4	2022-06-28	2022-07-03
28	123	963253263	2022-05-16	109.50	109.50	3	2022-06-15	2022-06-10